package huzh.foundation.dao;

import huzh.foundation.util.StringUtil;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *<p>title:DbUtil</p>
 *<p>description:</p>
 * @since 2014-6-7
 * @author huzh
 * @version 1.0
 */
public class DbUtil {
    /**
     * @param dsName
     * @param url
     * @return
     */
    public static Connection getWeblogicDataSourceConnection(String dsName, String url){
        Connection conn = null;
        try {
            Context ctx = null;
            Hashtable<String,String> ht = new Hashtable<String,String>();
            ht.put("java.naming.factory.initial", "weblogic.jndi.WLInitialContextFactory");
            ht.put("java.naming.provider.url", url);
            ctx = new InitialContext(ht);
            DataSource ds = (DataSource)ctx.lookup(dsName);
            conn = ds.getConnection();
        } catch (Exception ex){
            System.out.println("Exception at getWeblogicDataSourceConnection: " + ex.getMessage());
            ex.printStackTrace();
        }
        return conn;
    }

    /**
     * @param dbPara
     * @return
     */
    public static Connection getConnection(DbPara dbPara){
        try{
            Class.forName(dbPara.getDriverName());
            Connection connection = DriverManager.getConnection(dbPara.getUrl(), dbPara.getUser(), dbPara.getPassword());
            return connection;
        }catch (ClassNotFoundException cnfe) {
            System.err.println("Error loading driver:" + cnfe);
            return null;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            System.err.println("Error connecting: " + sqle);
        }
        return null;
    }

    /**
     * @param connection
     * @param sqlClause
     * @param close
     * @return
     */
    public static DbResults getQueryResults(Connection connection, String sqlClause, boolean close) {
        try{
            DatabaseMetaData dbMetaData = connection.getMetaData();
            String productName = dbMetaData.getDatabaseProductName();
            String productVersion = dbMetaData.getDatabaseProductVersion();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sqlClause);
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            String[] columnNames = new String[columnCount];

            for (int i = 1; i < columnCount + 1; i++) {
                columnNames[(i - 1)] = resultSetMetaData.getColumnName(i).trim();
            }
            DbResults dbResults = new DbResults(connection, productName, productVersion, resultSet, columnCount, columnNames);
            while (resultSet.next()) {
                String[] row = new String[columnCount];
                for (int i = 1; i < columnCount + 1; i++) {
                    String entry = StringUtil.nvl(resultSet.getString(i));
                    row[(i - 1)] = entry;
                }
                 dbResults.addRow(row);
            }
            statement.close();
            if (close) {
                connection.close();
            }
            return dbResults;
        }catch (Exception sqle) {
            sqle.printStackTrace();
            System.err.println("Error querying:" + sqle);
        }return null;
    }

    /**
     * @param dbPara
     * @param sqlClause
     * @param close
     * @return
     */
    public static DbResults getQueryResults(DbPara dbPara, String sqlClause, boolean close){
         Connection connection = getConnection(dbPara);
         if (connection != null) {
             return getQueryResults(connection, sqlClause, close);
         }
         return null;
    }

    /**
     * @param connection
     * @param sqlClause
     * @param close
     * @return
     */
    public static int executeUpdate(Connection connection, String sqlClause, boolean close) {
        try {
            Statement statement = connection.createStatement();
            int result = statement.executeUpdate(sqlClause);
            statement.close();
            if (close) {
                connection.close();
            }
            return result;
        }catch (SQLException sqle) {
            System.err.println("Error updating:" + sqle);
        }
        return -1;
    }

    /**
     * @param dbPara
     * @param sqlClause
     * @param close
     * @return
     */
    public static int executeUpdate(DbPara dbPara, String sqlClause, boolean close) {
        Connection connection = getConnection(dbPara);
        if (connection != null) {
            return executeUpdate(connection, sqlClause, close);
        }
        return -1;
    }

    /**
     * @param connection
     * @param tableName
     * @param entryWidth
     * @param printMetaData
     * @param close
     */
    public static void printTable(Connection connection, String tableName, int entryWidth,
            boolean printMetaData, boolean close){
        String sql = "select * from " + tableName;
        DbResults drs = getQueryResults(connection, sql, close);
        drs.PrintQueryResults(tableName, entryWidth, printMetaData);
    }
}
